# encoding: utf-8

import os
import openpyxl
import warnings
import pandas as pd
import xlwt
from xlsxwriter import *
warnings.filterwarnings('ignore')

class ExcelChangeUtil:

    def __init__(self):
        pass

    @classmethod
    def exceldelrows(cls, filename, i, a=1): # 多行删除(a为删除的行数，x为开始的行号,若只删除一行则a=1)
        workbook = openpyxl.load_workbook(filename)  # 返回一个workbook数据类型的值
        # workbook = xlrd.open_workbook(filename)
        sheet = workbook.active # 获取活动表
        sheet.delete_rows(idx=i,amount=a)
        workbook.save(filename)
        workbook.close()
        return 0

    @classmethod
    def exceldelcols(cls, filename, i, a=1):  # 多列删除(a为删除的列数，x为开始的列号,若只删除一列则a=1)
        workbook = openpyxl.load_workbook(filename)  # 返回一个workbook数据类型的值
        # workbook = xlrd.open_workbook(filename)
        sheet = workbook.active  # 获取活动表
        sheet.delete_cols(idx=i, amount=a)
        workbook.save(filename)
        workbook.close()
        return 0

    @classmethod
    def xls_del_rows(cls,filename,a=1,b=1): # a为开始行号，b为结束行号，默认为1
        # 获取sheet表名，存入sheetname列表
        sheetnames = pd.read_excel(filename, index_col=None, header=None,
                                      sheet_name=None).keys()
        sheetname = []
        for i in sheetnames:
            sheetname.append(i)
        # 修改excel，默认第一个表
        df = pd.read_excel(filename, index_col=None, header=None)
        for i in range(a-1,b):
            df.drop(labels = i,axis = 0,inplace = True)
        df.to_excel(filename,sheet_name= sheetname[0],index = False,header = False)

    @classmethod
    def xls_del_cols(cls,filename,a=1,b=1):# a为开始列号，b为结束列号，默认为1
        # 获取sheet表名，存入sheetname列表
        sheetnames = pd.read_excel(filename, index_col=None, header=None,
                                   sheet_name=None).keys()
        sheetname = []
        for i in sheetnames:
            sheetname.append(i)
        # 修改excel，默认第一个表
        df = pd.read_excel(filename, index_col=None, header=None)
        for i in range(a - 1, b):
            df.drop(labels=i, axis=1, inplace=True)
        df.to_excel(filename, sheet_name=sheetname[0], index=False, header=False)


# 测试代码
# df = pd.read_excel('C:\\Users\\HIAPAD\\Desktop\\test.xls', index_col=None,header=None,sheet_name=None)
# sheetnamelist = pd.read_excel('C:\\Users\\HIAPAD\\Desktop\\test.xls', index_col=None,header=None,sheet_name=None).keys()
# sheetname = []
# for i in sheetnamelist:
#     sheetname.append(i)
# print(sheetname[0])
# # print(df)
# print('*****************************************')
# # 删除行
# df1 = df.drop(labels=[0,1,2,3,4],axis=0)
# # 删除列
# df1 = df1.drop(labels=[0,1,2],axis=1)
# # df1 = df1.drop(labels= [0,1,2])
# print(df1)
# print('*****************************************')
# writer = pd.ExcelWriter('C:\\Users\\HIAPAD\\Desktop\\test.xls',engine='xlsxwriter')
# df1.to_excel(writer,sheet_name='sheet1',index=False,header=False)
# writer.save()